package com.mokylin.dbschema.dao;

import com.mokylin.dbschema.pojo.DbVersion;
import com.mokylin.dbschema.config.ConfigInfo;
import com.mokylin.dbschema.util.DBUtils;
import com.mokylin.dbschema.util.GlobalUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;

/**
 * @author 李朝(Li.Zhao)
 * @since 2015/8/31.
 */

public class DbVersionDao {

    private static final Logger LOG = LoggerFactory.getLogger(DbVersionDao.class);

    public static final int CURRENT_VERSION_ID = 1;

    private Connection getConn() {
        return DBUtils.getConn();
    }


    public DbVersion getDbVersion() throws SQLException {
        DbVersion dbVersion = null;
        String sql = String.format("SELECT * FROM t_db_version where `%1$s`=" + CURRENT_VERSION_ID, DbVersion.DbColumn.ID);
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = getConn().prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                dbVersion = new DbVersion();
                dbVersion.setId(resultSet.getInt(DbVersion.DbColumn.ID));
                dbVersion.setVersion(resultSet.getString(DbVersion.DbColumn.VERSION));
                dbVersion.setStatus(DbVersion.Status.valueOf(resultSet.getInt(DbVersion.DbColumn.STATUS)));
                dbVersion.setBuildingServerMac(resultSet.getString(DbVersion.DbColumn.BUILDING_SERVER_MAC));
                dbVersion.setDesc(resultSet.getString(DbVersion.DbColumn.DESC));
                dbVersion.setUpdateTime(resultSet.getTimestamp(DbVersion.DbColumn.UPDATE_TIME));
            }
            return dbVersion;
        } finally {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
        }
    }

    public boolean modifyIncrementId() throws SQLException {
        String sql = "ALTER TABLE `t_db_version` MODIFY COLUMN `id`  int(11) AUTO_INCREMENT ";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = getConn().prepareStatement(sql);
            return preparedStatement.execute();
        } finally {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
        }
    }


    public boolean createTable() throws SQLException {
        String createSql = String.format("CREATE TABLE t_db_version (" +
                        "  `%1$s` INT(11) AUTO_INCREMENT," +
                        "  `%2$s` VARCHAR(255) DEFAULT NULL," +
                        "  `%3$s` INT(11) DEFAULT NULL," +
                        "  `%4$s` VARCHAR(255) DEFAULT NULL," +
                        "  `%5$s` VARCHAR(255) DEFAULT NULL," +
                        "  `%6$s` DATETIME DEFAULT NULL," +
                        "  PRIMARY KEY (`id`)" +
                        ")",
                DbVersion.DbColumn.ID, DbVersion.DbColumn.VERSION, DbVersion.DbColumn.STATUS,
                DbVersion.DbColumn.BUILDING_SERVER_MAC, DbVersion.DbColumn.DESC, DbVersion.DbColumn.UPDATE_TIME
        );
        PreparedStatement createStmt = null;
        try {
            createStmt = getConn().prepareStatement(createSql);
            createStmt.execute();
        } finally {
            if (createStmt != null) {
                createStmt.close();
            }
        }
        return true;
    }


    public boolean insert(DbVersion dbVersion) throws SQLException {
        String sql = String.format("insert into t_db_version(`%1$s`,`%2$s`,`%3$s`,`%4$s`,`%5$s`) values(" + CURRENT_VERSION_ID + ",?,?,?,?)"
                , DbVersion.DbColumn.ID, DbVersion.DbColumn.VERSION, DbVersion.DbColumn.STATUS, DbVersion.DbColumn.BUILDING_SERVER_MAC, DbVersion.DbColumn.UPDATE_TIME);
        PreparedStatement insertStmt = null;
        try {
            insertStmt = getConn().prepareStatement(sql);
            insertStmt.setString(1, dbVersion.getVersion());
            insertStmt.setInt(2, dbVersion.getStatus().getVal());
            insertStmt.setString(3, dbVersion.getBuildingServerMac());
            insertStmt.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
            insertStmt.execute();
        } finally {
            if (insertStmt != null) {
                insertStmt.close();
            }
        }
        return true;
    }

    /**
     * 插入版本升级日志（id！=1的记录）
     *
     * @param dbVersionLog
     * @throws SQLException
     */
    public boolean insertLog(DbVersion dbVersionLog) throws SQLException {
        String sql = String.format("insert into t_db_version(`%1$s`,`%2$s`,`%3$s`,`%4$s`,`%5$s`) values(?,?,?,?,?)"
                , DbVersion.DbColumn.VERSION, DbVersion.DbColumn.STATUS, DbVersion.DbColumn.BUILDING_SERVER_MAC, DbVersion.DbColumn.UPDATE_TIME, DbVersion.DbColumn.DESC);
        PreparedStatement insertStmt = null;
        try {
            insertStmt = getConn().prepareStatement(sql);
            insertStmt.setString(1, dbVersionLog.getVersion());
            insertStmt.setInt(2, dbVersionLog.getStatus().getVal());
            insertStmt.setString(3, dbVersionLog.getBuildingServerMac());
            insertStmt.setTimestamp(4, new Timestamp(dbVersionLog.getUpdateTime().getTime()));
            insertStmt.setString(5, dbVersionLog.getDesc());
            insertStmt.execute();
        } finally {
            if (insertStmt != null) {
                insertStmt.close();
            }
        }
        return true;
    }

    public boolean setPreparedStatus() throws SQLException {
        String sql = String.format("UPDATE t_db_version " +
                        "SET `%1$s`=" + DbVersion.Status.PREPARED.getVal() + ",`%2$s`=?,`%3$s`=?,`%4$s`=\"\" " +
                        "WHERE `%5$s`=" + CURRENT_VERSION_ID,
                DbVersion.DbColumn.STATUS, DbVersion.DbColumn.BUILDING_SERVER_MAC, DbVersion.DbColumn.UPDATE_TIME, DbVersion.DbColumn.DESC, DbVersion.DbColumn.ID);
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = getConn().prepareStatement(sql);
            preparedStatement.setString(1, GlobalUtils.getServerIdentify());
            preparedStatement.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
            return preparedStatement.executeUpdate() == 1;
        } finally {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
        }
    }

    public boolean convertNormal2PreparedStatus() throws SQLException {
        String sql = String.format("UPDATE t_db_version " +
                        "SET `%1$s`=" + DbVersion.Status.PREPARED.getVal() + ",`%2$s`=?,`%3$s`=? " +
                        "WHERE `%4$s`=" + CURRENT_VERSION_ID + " AND `%1$s`=" + DbVersion.Status.NORMAL.getVal(),
                DbVersion.DbColumn.STATUS, DbVersion.DbColumn.BUILDING_SERVER_MAC, DbVersion.DbColumn.UPDATE_TIME, DbVersion.DbColumn.ID);
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = getConn().prepareStatement(sql);
            preparedStatement.setString(1, GlobalUtils.getServerIdentify());
            preparedStatement.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
            return preparedStatement.executeUpdate() == 1;
        } finally {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
        }
    }

    public boolean convertError2PreparedStatus() throws SQLException {
        String sql = String.format("UPDATE t_db_version " +
                        "SET `%1$s`=" + DbVersion.Status.PREPARED.getVal() + ",`%2$s`=?,`%3$s`=? " +
                        "WHERE `%4$s`=" + CURRENT_VERSION_ID + " AND `%1$s`=" + DbVersion.Status.ERROR.getVal(),
                DbVersion.DbColumn.STATUS, DbVersion.DbColumn.BUILDING_SERVER_MAC, DbVersion.DbColumn.UPDATE_TIME, DbVersion.DbColumn.ID);
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = getConn().prepareStatement(sql);
            preparedStatement.setString(1, GlobalUtils.getServerIdentify());
            preparedStatement.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
            return preparedStatement.executeUpdate() == 1;
        } finally {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
        }
    }

    public boolean updateBuildingStatus(String version) throws SQLException {
        String sql = String.format("UPDATE t_db_version " +
                        "SET `%1$s`=" + DbVersion.Status.BUILDING.getVal() + ",`%2$s`=?,`%3$s`=?,`%4$s`=? " +
                        "WHERE " +
                        " `%5$s`=" + CURRENT_VERSION_ID,
                DbVersion.DbColumn.STATUS, DbVersion.DbColumn.VERSION, DbVersion.DbColumn.BUILDING_SERVER_MAC, DbVersion.DbColumn.UPDATE_TIME, DbVersion.DbColumn.ID);
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = getConn().prepareStatement(sql);
            preparedStatement.setString(1, version);
            preparedStatement.setString(2, GlobalUtils.getServerIdentify());
            preparedStatement.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
            return 1 == preparedStatement.executeUpdate();
        } finally {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
        }
    }

    public boolean convertBuilding2NormalStatus() throws SQLException {
        String sql = String.format("UPDATE t_db_version " +
                        "SET `%1$s`=" + DbVersion.Status.NORMAL.getVal() + ",`%2$s`=?,`%3$s`=?,`%4$s`=? " +
                        "WHERE `%5$s`=" + CURRENT_VERSION_ID,
                DbVersion.DbColumn.STATUS, DbVersion.DbColumn.VERSION, DbVersion.DbColumn.BUILDING_SERVER_MAC, DbVersion.DbColumn.UPDATE_TIME, DbVersion.DbColumn.ID);
//            +                    " AND `status`=" + DbVersion.Status.BUILDING.getVal();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = getConn().prepareStatement(sql);
            preparedStatement.setString(1, ConfigInfo.getNewestVersion());
            preparedStatement.setString(2, GlobalUtils.getServerIdentify());
            preparedStatement.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
            return 1 == preparedStatement.executeUpdate();
        } finally {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
        }
    }

    public boolean setErrorStatus(String desc) throws SQLException {
        String sql = String.format("UPDATE t_db_version SET " +
                        " `status`=" + DbVersion.Status.ERROR.getVal() + ",`%1$s`=?,`%2$s`=?,`%3$s`=? " +
                        " WHERE `%4$s`=" + CURRENT_VERSION_ID,
                DbVersion.DbColumn.BUILDING_SERVER_MAC, DbVersion.DbColumn.DESC, DbVersion.DbColumn.UPDATE_TIME, DbVersion.DbColumn.ID);
//            +                    "AND `status`=" + DbVersion.Status.BUILDING.getVal();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = getConn().prepareStatement(sql);
            preparedStatement.setString(1, GlobalUtils.getServerIdentify());
            preparedStatement.setString(2, desc);
            preparedStatement.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
            return 1 == preparedStatement.executeUpdate();
        } finally {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
        }
    }

    public boolean setUpdateTime() throws SQLException {
        String sql = String.format("UPDATE t_db_version SET `%1$s`=?,`%2$s`=? where `%3$s`=" + CURRENT_VERSION_ID, DbVersion.DbColumn.UPDATE_TIME, DbVersion.DbColumn.BUILDING_SERVER_MAC, DbVersion.DbColumn.ID);
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = getConn().prepareStatement(sql);
            preparedStatement.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
            preparedStatement.setString(2, GlobalUtils.getServerIdentify());
            return 1 == preparedStatement.executeUpdate();
        } finally {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
        }
    }


    /**
     * 强制执行sql方法，如果没有t_db_version这个表则创建一个表，并插入初始版本信息
     *
     * @param fn
     */
    public <T> T forceExec(SqlRunner<T> fn) {
        try {
            return fn.run();
        } catch (Exception e) {
            LOG.warn(e.getMessage(), e);
            if (e instanceof SQLSyntaxErrorException) {
                try {
                    createTable();
                } catch (SQLException e1) {
                    LOG.warn(e.getMessage(), e);
                }
                try {
                    insert(DbVersion.getInitDbVersion());
                } catch (SQLException e1) {
                    LOG.warn(e.getMessage(), e);
                }
                try {
                    return fn.run();
                } catch (SQLException e1) {
                    LOG.error(e.getMessage(), e);
                }
            }
        }
        return null;
    }

    @FunctionalInterface
    public static interface SqlRunner<T> {
        T run() throws SQLException;
    }


}
